package com.wHitE.dao.impl;

import com.wHitE.dao.BookListDao;
import com.wHitE.pojo.Book;
import com.wHitE.pojo.BookList;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookListDaoImpl implements BookListDao {
    private int count;
    private List<Book> bookList = new ArrayList<>();

    @Override
    public BookList bookList(int pageNum, int pageCount) {
        try {
            // 连接（JDBC）数据库并查询数据以及删除从数据库（MySQL）中
            // 1.加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2.连接数据库
            Connection connection = DriverManager.getConnection("jdbc:mysql:" +
                    "//localhost:3306/javaweb_database", "root", "123456");
            // 查询
            // 发送SQL语句
            String sql0 = "select count(*) from book";
            String sql = "select * from book limit ?,? ";
            PreparedStatement pst0 = connection.prepareStatement(sql0);
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setInt(1, (pageNum - 1) * pageCount);
            pst.setInt(2, pageCount);
            // 执行
            ResultSet resultSet0 = pst0.executeQuery();
            ResultSet resultSet = pst.executeQuery();
            // 处理查询结果
            bookList.clear();
            while (resultSet0.next()) {
                count = resultSet0.getInt(1);
            }
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int quantity = resultSet.getInt("quantity");
                Double price = resultSet.getDouble("price");
                String name = resultSet.getString("name");
                String coverImg = resultSet.getString("coverImg");
                String author = resultSet.getString("author");
                String uploader = resultSet.getString("uploader");
                Date updateTime = resultSet.getDate("updateTime");
                bookList.add(new Book(price, name, coverImg, author, uploader, id, quantity, updateTime));
            }
            // 关闭执行
            pst0.close();
            pst.close();
            // 断开连接
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return new BookList(count, bookList);
    }

    @Override
    public BookList bookList(String name_, int pageNum, int pageCount) {
        try {
            // 连接（JDBC）数据库并查询数据以及删除从数据库（MySQL）中
            // 1.加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2.连接数据库
            Connection connection = DriverManager.getConnection("jdbc:mysql:" +
                    "//localhost:3306/javaweb_database", "root", "123456");
            // 查询
            // 发送SQL语句
            String sql0 = "select count(*) from book";
            String sql = "select * from book where name like ?  limit ?,?";
            PreparedStatement pst0 = connection.prepareStatement(sql0);
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setString(1, "%" + name_ + "%");
            pst.setInt(2, (pageNum - 1) * pageCount);
            pst.setInt(3, pageCount);
            // 执行
            ResultSet resultSet0 = pst0.executeQuery();
            ResultSet resultSet = pst.executeQuery();
            // 处理查询结果
            bookList.clear();
            while (resultSet0.next()) {
                count = resultSet0.getInt(1);
            }
            bookList.clear();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                int quantity = resultSet.getInt("quantity");
                Double price = resultSet.getDouble("price");
                String name = resultSet.getString("name");
                String coverImg = resultSet.getString("coverImg");
                String author = resultSet.getString("author");
                String uploader = resultSet.getString("uploader");
                Date updateTime = resultSet.getDate("updateTime");
                bookList.add(new Book(price, name, coverImg, author, uploader, id, quantity, updateTime));
            }
            // 关闭执行
            pst0.close();
            pst.close();
            // 断开连接
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return new BookList(count, bookList);
    }
}